PostgreSQL
Create user postgres to access PostgreSQL * sudo -u postgres psql Create table for countries: * CREATE TABLE countries ( * country_code char(2) PRIMARY KEY, * country_name text UNIQUE * ); Add data for countries: * INSERT INTO countries (country_code, country_name) * VALUES ('us','United States'), ('mx','Mexico'), ('au','Australia'), ('gb','United Kingdom'),('de','Germany'),('ee','Estonia'); Create table for cities: * CREATE TABLE cities ( * name text NOT NULL, * postal_code varchar(9) CHECK (postal_code <> ''), * country_code char(2) REFERENCES countries, * PRIMARY KEY (country_code, postal_code) * ); Add data for cities: * INSERT INTO cities * VALUES ('Portland','97206','us'); Create table for venues: * CREATE TABLE venues ( * venue_id SERIAL PRIMARY KEY, * name varchar(255), * street_address text, * type char(7) CHECK (type in ('public','private')) DEFAULT 'public', * active boolean DEFAULT TRUE, * postal_code varchar(9), * country_code char(2), * FOREIGN KEY (country_code, postal_code) * REFERENCES cities (country_code, postal_code) MATCH FULL * ); Add data for venues * INSERT INTO venues (name, street_address, postal_code, country_code) * VALUES ('Crystal Ballroom','97206','us'); * INSERT INTO venues (name, postal_code, country_code) * VALUES ('Voodoo Doughnut','97206','us') * INSERT INTO venues (name, street_address, postal_code, country_code) * VALUES ('My Place', 'Penn Avenue', 'NW11 0LR', 'gb'); Create table for events: * CREATE TABLE events ( * events_id SERIAL PRIMARY KEY, * title text, * starts timestamp, * ends timestamp, * venue_id integer, * FOREIGN KEY (venue_id) * REFERENCES venues (venue_id) * ); Add data for events * INSERT INTO events (title, starts, ends, venue_id) * VALUES ('Fight Club','2018-02-15 17:30:00','2018-02-15 19:30:00', 2), * ('April Fools Day', '2018-04-01 00:00:00','2018-04-01 23:59:00', NULL), * ('Christmas Day', '2018-02-15 19:30:00', '2018-12-25 23:59:00', NULL), * ('Moby', '2018-02-06 21:00', '2018-02-06 23:00', (SELECT venue_id FROM venues * WHERE name = 'Crystal Ballroom')), * ('Wedding', '2018-02-26 21:00','2018-02-26 23:00', 2), * ('Dinner with Mom', '2018-02-26 18:00:00','2018-02-26 20:30:00', 3), * ('Valentine''s Day','2018-02-14 00:00:00', '2018-02-14 23:59:00', NULL); Create countries table with columns 'country code', and 'country name' * CREATE TABLE countries ( * country_code char(2) PRIMARY KEY, * country_name text UNIQUE * ); Insert data into countries table * INSERT INTO countries (country_code, country_name) * VALUES ('us','United States'),('mx','Mexico'),('au','Australia'), * ('gb','United Kingdom'),('de','Germany'),('ll','Loompaland'); Delete 'll' from countries table * DELETE FROM countries * WHERE country_code = 'll'; Create cities table with columns 'name', 'postal code', and 'country code' * CREATE TABLE cities ( * name text NOT NULL, * postal_code varchar(9) CHECK (postal_code <> ''), * country_code char(2) REFERENCES countries, * PRIMARY KEY (country_code, postal_code) * ); Display tables * SELECT * * FROM countries; * SELECT * * FROM cities; Insert data into cities * INSERT INTO cities * VALUES ('Portland','87200','us'); Update cities * UPDATE cities * SET postal_code = '97206' * WHERE name = 'Portland'; Join 'countries' and 'cities' tables. Will display table of cities with country_name * SELECT cities.*, country_name * FROM cities INNER JOIN countries * ON cities.country_code = countries.country_code; Create 'venues' table with columns 'venue id', 'name', 'street address', 'type', 'postal code', and 'country code' * CREATE TABLE venues( * venue_id SERIAL PRIMARY KEY, * name varchar(255), * street_address text, * type char(7) CHECK (type in ('public','private')) DEFAULT 'public', * postal_code varchar(9), * country_code char(2), * FOREIGN KEY (country_code, postal_code) * REFERENCES cities (country_code, postal_code) MATCH * FULL * ); Insert data into table 'venues' * INSERT INTO venues (name, postal_code, country_code) * VALUES ('Crystal Ballroom','97206','us'); Display venue id, venue name, and city name * SELECT v.venue_id, v.name, c.name * FROM venues v INNER JOIN cities c * ON v.postal_code=c.postal_code AND * v.country_code=c.country_code; insert data for 'Voodoo Doughnut' into 'Venues' and return 'Venue ID' * INSERT INTO venues (name, postal_code, country_code) * VALUES ('Voodoo Doughnut','97206','us') RETURNING * venue_id; Create 'events' table * CREATE TABLE events ( * events_id SERIAL PRIMARY KEY, * title text, * starts timestamp, * ends timestamp, * active boolean DEFAULT TRUE, * venue_id integer, * FOREIGN KEY (venue_id) * REFERENCES venues (venue_id) * ); Insert data into table 'events' * INSERT INTO events (title, starts, ends, venue_id, event_id) * VALUES ('FIGHT CLUB', '2018-02-15 17:30:00', '2018-02-15 19:30:00', '2', '1'); * INSERT INTO events (title, starts, ends, venue_id, event_id) * VALUES ('April Fools Day','2018-04-01 00:00:00','2018-04-01 23:59:00', '2', '2'); * INSERT INTO events (title, starts, ends, venue_id, event_id) * VALUES ('Christmas Day', '2018-02-15 19:30:00', '2018-12-25 23:59:00', '2', '3'); Return 'event title' and 'venue name' * SELECT e.title, v.name * FROM events e LEFT JOIN venues v * ON e.venue_id = v.venue_id;